package cn.edu.zafu.bigdata.lyq.work10;

import java.sql.*;

public class DBUtil {
    private static final String URL = "jdbc:mysql://192.168.169.76:3306/learnjava";
    private static final String USER = "learn";
    private static final String PASSWORD = "learnpassword";

    // 建立数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 关闭资源（可扩展用于关闭Statement、ResultSet等更多资源）
    public static void close(Connection connection) {
        if (connection!= null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 创建学生信息表（假设表名为student_table_自己学号，此处以student_table为例）
    public static void createTable() {
        String createTableSql = "CREATE TABLE IF NOT EXISTS students202203090213 (" +
                "id VARCHAR(20) PRIMARY KEY," +
                "name VARCHAR(50)," +
                "phone VARCHAR(20)" +
                ")";
        try (Connection connection = getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(createTableSql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 添加学生信息到数据库
    public static void addStudent(Student student) {
        String insertSql = "INSERT INTO students202203090213 (id, name, phone) VALUES (?,?,?)";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
            preparedStatement.setString(1, student.getId());
            preparedStatement.setString(2, student.getName());
            preparedStatement.setString(3, student.getPhone());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            if (e.getMessage().contains("Duplicate entry")) {
                System.out.println("学号重复，请重新输入！");
            } else {
                e.printStackTrace();
            }
        }
    }

    // 根据学号检索学生信息
    public static Student getStudentById(String id) {
        String selectSql = "SELECT * FROM students202203090213 WHERE id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(selectSql)) {
            preparedStatement.setString(1, id);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    return new Student(resultSet.getString("id"),
                            resultSet.getString("name"),
                            resultSet.getString("phone"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据学号更新学生信息
    public static void updateStudent(Student student) {
        String updateSql = "UPDATE students202203090213 SET name =?, phone =? WHERE id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(updateSql)) {
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getPhone());
            preparedStatement.setString(3, student.getId());
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected == 0) {
                System.out.println("未找到该学生");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 根据学号删除学生信息
    public static void deleteStudent(String id) {
        String deleteSql = "DELETE FROM students202203090213 WHERE id =?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(deleteSql)) {
            preparedStatement.setString(1, id);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected == 0) {
                System.out.println("未找到该学生");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}